package com.ailanyin.updatesql.utils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Jdbc 工具类
 *
 * @author ailanyin
 */
public class DbUtil {

    private static Statement stmt = null;
    private static Connection connection = null;

    public static boolean runHandleSql(List<String> sqlList) {
        try {
            for (String sql : sqlList) {
                stmt.addBatch(sql);
            }
            stmt.executeBatch();
            connection.commit();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return false;
        }
    }

    /**
     * 查询返回List集合
     *
     * @param cls
     * @param sql
     * @param obj
     * @param <T>
     * @return List<T>
     */
    public static <T> List<T> getList(Class<T> cls, String sql, Object... obj) {

        PreparedStatement ps = null;
        ResultSet rs = null;
        List<T> list = new ArrayList<T>();
        try {
            //2.获取预处理对象
            ps = connection.prepareStatement(sql);
            //循环参数，如果没有就不走这里
            for (int i = 1; i <= obj.length; i++) {
                //注意：数组下标从0开始，预处理参数设置从1开始
                ps.setObject(i, obj[i - 1]);
            }
            //3.执行SQL语句
            rs = ps.executeQuery();
            //4.遍历结果集
            //遍历之前准备：因为封装不知道未来会查询多少列，所以我们需要指定有多少列
            //获取ResultSet对象的列编号、类型和属性
            ResultSetMetaData date = rs.getMetaData();
            //获取列数
            int column = date.getColumnCount();
            //获取本类所有的属性
            Field[] fields = cls.getDeclaredFields();
            //创建一个list集合对象来存储查询数据

            //开始遍历结果集
            while (rs.next()) {
                //创建类类型实例
                T t = cls.newInstance();
                for (int i = 1; i <= column; i++) {
                    //每一列的值
                    Object value = rs.getObject(i);
                    //获取每一列名称（别名）
                    String columnName = date.getColumnLabel(i);
                    //遍历所有属性对象
                    for (Field field : fields) {
                        //获取属性名
                        String name = field.getName();
                        field.setAccessible(true);
                        if (name.equals(columnName)) {
                            field.set(t, value);
                            break;
                        }
                    }
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            return null;
        }

    }


    public static void startConnection(String driver, String url,
                                       String user, String password) {
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, password);
            //JDBC中默认是true，自动提交事务（这里手动成false）
            connection.setAutoCommit(false);
            stmt = connection.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void closeConnection() {
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
